package com.ocom.mjrecode.service.impl;


import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dangdang.ddframe.rdb.sharding.api.HintManager;
import com.ocom.common.entity.access.EntityMjGroupInfo;
import com.ocom.common.entity.mjrecode.MjAttendance;
import com.ocom.common.entity.mjrecode.MjRecord;
import com.ocom.common.enums.ResultEnum;
import com.ocom.common.request.access.AddAttendanceReq;
import com.ocom.common.request.exposeapi.LargeScreenRequest;
import com.ocom.common.request.srvpaccess.UpdateAvatarRequest;
import com.ocom.common.resp.Result;
import com.ocom.common.utils.DateUtil;
import com.ocom.common.utils.DateUtils;
import com.ocom.common.utils.ResultUtil;
import com.ocom.common.utils.StringUtils;
import com.ocom.common.vo.human.UserInfoYkmVo;
import com.ocom.common.vo.recode.AccessRecordVo;
import com.ocom.common.vo.recode.AttendanceRecordVo;
import com.ocom.common.vo.recode.MjRecCountVo;
import com.ocom.mjrecode.feign.HumanClientService;
import com.ocom.mjrecode.mapper.MjRecodeMapper;
import com.ocom.mjrecode.service.MjAttendanceService;
import com.ocom.mjrecode.service.MjRecodeService;
import com.ocom.mjrecode.utils.CommonUtil;
import com.ocom.mjrecode.vo.EntryExitRecordsVo;
import com.ocom.mjrecode.vo.YmDataGroupByMachVo;
import com.ocom.redis.util.RedisUtil;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.shardingsphere.core.route.router.masterslave.MasterVisitedManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;
import java.util.stream.Collectors;


@Service
public class MjRecodeServiceImpl implements MjRecodeService {

    @Autowired
    private MjRecodeMapper mapper;

    @Autowired
    private HumanClientService humanClientService;

    @Autowired
    private MjAttendanceService mjAttendanceService;

    @Resource
    private RedisUtil redisUtil;

    @Override
    public List<MjRecord> findByGoods(QueryWrapper<MjRecord> queryWrapper) {
        return mapper.selectList(queryWrapper);
    }

    @Override
    public int batchAdd(List<MjRecord> goods) {
        int i = 0;
        for (MjRecord g : goods)
            i += mapper.insert(g);
        return i;
    }

    @Override
    public int add(MjRecord mjRecord) {
        return mapper.insert(mjRecord);
    }

    @Override
    public MjRecord getByGoodsId(Long id) {
        QueryWrapper<MjRecord> queryWrapper1 = new QueryWrapper<>();
        queryWrapper1.select("distinct device_no", "com_id", "area_name", "region_name");
        queryWrapper1.eq("month_value", "202204");
        queryWrapper1.eq("com_id", 1);
        List<MjRecord> list1 = mapper.selectList(queryWrapper1);

//        QueryWrapper<MjRecord> queryWrapper1 = new QueryWrapper<>();
//        queryWrapper1.select("count(device_no) as sum1","com_id");
//        queryWrapper1.eq("month_value","202204");
//        queryWrapper1.eq("com_id",1);
//        queryWrapper1.groupBy("device_no");

//        List<MjRecord> list1 = mapper.selectList(queryWrapper1);

        System.out.println(list1);


        HintManager.getInstance().setMasterRouteOnly();
        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("month_value", "202204");
        queryWrapper.eq("com_id", 501);
        queryWrapper.eq("id", 6914553167867027456l);

        //强制读主库  shardingsphere
        MasterVisitedManager.setMasterVisited();

        MjRecord mjRecord = mapper.selectOne(queryWrapper);


        return mjRecord;
    }


    @Override
    public Result GetAccessRecord(AccessRecordVo accessRecordVo) {

        if (accessRecordVo.getBeginTime() == null || accessRecordVo.getBeginTime().length() < 7) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }
        if (accessRecordVo.getEndTime() == null || accessRecordVo.getEndTime().length() < 7) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }

        String start = accessRecordVo.getBeginTime().substring(0, 7).replace("-", "");
        String end = accessRecordVo.getEndTime().substring(0, 7).replace("-", "");

        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }
        if (dates.size() > 6) {
            return ResultUtil.error(ResultEnum.Err_2034);
        }

        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();

        queryWrapper.le("open_time", DateUtils.strToDate(accessRecordVo.getEndTime()));
        queryWrapper.ge("open_time", DateUtils.strToDate(accessRecordVo.getBeginTime()));

        List<String> finalDates = dates;

        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });


        //条件查询
        if (!CommonUtil.isNull(accessRecordVo.getDeviceNo())) {
            queryWrapper.eq("device_no", accessRecordVo.getDeviceNo());
        }
        if (!CommonUtil.isNull(accessRecordVo.getCodeColor())) {
            queryWrapper.eq("code_color", accessRecordVo.getCodeColor());
        }
        if (!CommonUtil.isNull(accessRecordVo.getDeviceNo())) {
            queryWrapper.eq("device_no", accessRecordVo.getDeviceNo());
        }
        if (!CommonUtil.isNull(accessRecordVo.getPCode())) {
            queryWrapper.eq("p_code", accessRecordVo.getPCode());
        }
        if (!CommonUtil.isNull(accessRecordVo.getPName())) {
            queryWrapper.eq("p_name", accessRecordVo.getPName());
        }
        if (accessRecordVo.getAreaNo() != null && accessRecordVo.getAreaNo() > 0) {
            queryWrapper.eq("area_id", accessRecordVo.getAreaNo());
        }
        if (accessRecordVo.getComId() != null && accessRecordVo.getComId() > 0) {
            queryWrapper.eq("com_id", accessRecordVo.getComId());
        }
        if (accessRecordVo.getPId() != null && accessRecordVo.getPId() > 0) {
            queryWrapper.eq("p_id", accessRecordVo.getPId());
        }
        if (accessRecordVo.getUserId() != null && accessRecordVo.getUserId() > 0) {
            queryWrapper.eq("p_id", accessRecordVo.getUserId());
        }

        if (accessRecordVo.getId() != null && accessRecordVo.getId() > 0l) {
            queryWrapper.eq("id", accessRecordVo.getId());
        }


        if (accessRecordVo.getPType() != null && accessRecordVo.getPType() > 0l) {
            queryWrapper.eq("p_type", accessRecordVo.getPType());
        }
        if (accessRecordVo.getAreaId() != null && accessRecordVo.getAreaId() > 0) {
            queryWrapper.eq("area_id", accessRecordVo.getAreaId());
        }
        if (accessRecordVo.getRegionId() != null && accessRecordVo.getRegionId() > 0) {
            queryWrapper.eq("region_id", accessRecordVo.getRegionId());
        }
        if (accessRecordVo.getDeptId() != null && accessRecordVo.getDeptId() > 0) {
            queryWrapper.eq("dept_id", accessRecordVo.getDeptId());
        }
        if (accessRecordVo.getOpenType() != null && accessRecordVo.getOpenType() >= 0) {
            queryWrapper.eq("open_type", accessRecordVo.getOpenType());
        }
        if (accessRecordVo.getDeptIds() != null && accessRecordVo.getDeptIds().size() > 0) {
            queryWrapper.in("dept_id", accessRecordVo.getDeptIds());
        }



        if (!CommonUtil.isNull(accessRecordVo.getYctRetcode())) {
            queryWrapper.eq("yct_retcode", accessRecordVo.getYctRetcode());
        }
        if (StringUtils.isNotNull(accessRecordVo.getOpenState())) {
            queryWrapper.eq("open_state", accessRecordVo.getOpenState());
        }

//        if (!CommonUtil.isNull(accessRecordVo.getParaSum())) {
//
//            String[] paraSums = accessRecordVo.getParaSum().split(",");
//
//            //接口所传参数
//            List<String> paraLists = Arrays.asList(paraSums);
//
//            //有效参数
//            List<String> userfulList = new ArrayList<>();
//            userfulList.add("pullFailed");
//            userfulList.add("other");
//            userfulList.add("red");
//            userfulList.add("yellow");
//            userfulList.add("green");
//            userfulList.add("positive");
//            userfulList.add("negative");
//            userfulList.add("noNucleic");
//            userfulList.add("hours24");
//            userfulList.add("hours48");
//            userfulList.add("hours72");
//            userfulList.add("within7Days");
//            userfulList.add("more7Days");
//            userfulList.add("ym0");
//            userfulList.add("ym1");
//            userfulList.add("ym2");
//            userfulList.add("ym3");
//            userfulList.add("ymMore3");
//
//            //查询是否有交集
//            List<String> origin = new ArrayList<>();
//            origin.addAll(paraLists);
//            origin.retainAll(userfulList);
//
//            //存在交集
//            if (origin.size() > 0) {
//                //有效查询条件
//                Map<String, String> userfulQuery = new HashMap<>();
//                userfulQuery.put("pullFailed", "or (p_id = 0 and yct_retcode != '200') ");
//                userfulQuery.put("other", "or code_color not in ('红','黄','绿') ");
//                userfulQuery.put("red", "or code_color = '红' ");
//                userfulQuery.put("yellow", "or code_color = '黄' ");
//                userfulQuery.put("green", "or code_color = '绿' ");
//                userfulQuery.put("positive", "or hs_result = '阳性' ");
//                userfulQuery.put("negative", "or hs_result = '阴性' ");
//                userfulQuery.put("noNucleic", "or hs_result not in ('阳性','阴性')  ");
//                userfulQuery.put("hours24", "or hs_result='阴性' and TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) <=24 ");
//                userfulQuery.put("hours48", "or hs_result='阴性' and TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  between 24+1  and 24*2 ");
//                userfulQuery.put("hours72", "or hs_result='阴性' and TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  between 24*2+1 and 24*3 ");
//                userfulQuery.put("within7Days", "or hs_result='阴性' and TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))  between 24*3+1 and 24*7 ");
//                userfulQuery.put("more7Days", "or hs_result='阴性' and TIMESTAMPDIFF(HOUR,hs_time,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) > 24*7 ");
//                userfulQuery.put("ym0", "or ym_num=0 ");
//                userfulQuery.put("ym1", "or ym_num=1 ");
//                userfulQuery.put("ym2", "or ym_num=2 ");
//                userfulQuery.put("ym3", "or ym_num=3 ");
//                userfulQuery.put("ymMore3", "or ym_num>3 ");
//
//                StringBuilder applySqlBuilder = new StringBuilder();
//                for (String key : origin) {
//                    applySqlBuilder.append(userfulQuery.get(key));
//                }
//                String applySql = applySqlBuilder.toString();
//                applySql = StringUtils.trimStart(applySql, "or");
//
//                queryWrapper.apply("(" + applySql + ")");
//            }
//
//        }

        queryWrapper.orderByDesc("open_time");
        //强制读主库  shardingsphere
        // MasterVisitedManager.setMasterVisited();
        Page<MjRecord> result1 = mapper.selectPage(new Page<>(accessRecordVo.getPage(), accessRecordVo.getSize()), queryWrapper);
        return ResultUtil.success(result1);


    }

    @Override
    public Result getAttendanceRecord(AttendanceRecordVo request) {
        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();

        queryWrapper.eq("com_id", request.getComId());

        String dateTime = DateUtils.dateTimeNow(DateUtils.YYYYMM);
        if (StringUtils.isNotBlank(request.getBeginTime())) {
            try {
                dateTime = DateFormatUtils.format(DateUtils.parseDate( request.getBeginTime()), DateUtils.YYYYMM);
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        queryWrapper.eq("month_value", dateTime);

        if (request.getId() != null && request.getId() > 0l) {
            queryWrapper.gt("id", request.getId());//雪花ID可能出现排序问题
        }
        if (StringUtils.isNotBlank(request.getBeginTime())) {
            queryWrapper.gt("open_time", request.getBeginTime());
        }
        if (request.getDeviceVerType() != null) {
            queryWrapper.eq("device_ver_type", request.getDeviceVerType());
        }
        queryWrapper.ne("p_id", 0);

        queryWrapper.orderByAsc("id");
        queryWrapper.last("limit " + request.getSize());

//        Page<MjRecord> result = mapper.selectPage(new Page<>(request.getPage(), request.getSize()), queryWrapper);
        return ResultUtil.success(mapper.selectList(queryWrapper));
    }


    @Override
    public Integer getRecodeNum(Long companyId) {
        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();
        String date = DateUtils.getDate().substring(0, 7).replace("-", "");
        queryWrapper.eq("month_value", date);
        queryWrapper.eq("com_id", companyId);
        queryWrapper.ge("open_time", DateUtils.strToDate(DateUtils.getDate() + " 00:00:00"));
        queryWrapper.le("open_time", DateUtils.strToDate(DateUtils.getDate() + " 23:59:59"));
        return mapper.selectCount(queryWrapper);
    }

    // 依据设备获取统计列表 （getYmDataGroupByMach）
    @Override
    public Result getYmDataGroupByMach(YmDataGroupByMachVo ymDataGroupByMachVo) {
        if (ymDataGroupByMachVo.getStartTime() == null || ymDataGroupByMachVo.getStartTime().length() < 7) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }
        if (ymDataGroupByMachVo.getEndTime() == null || ymDataGroupByMachVo.getEndTime().length() < 7) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }
        QueryWrapper<MjRecord> query = Wrappers.query();
        query.select("device_no,com_id,region_id,area_id,area_name,region_name," +
                "count(open_state=1 or NULL) as openNum,count(open_state=2 or NULL) as unOpenNum, " +
                "count(code_color='绿' or NULL) as greenNum," +
                "count(code_color='黄' or NULL) as yellowNum," +
                "count(code_color='红' or NULL) as redNum," +
                "count(hs_result='阴性' or NULL) as hsYinNum,count(hs_result='阳性' or NULL) as hsYangNum");
        String start = ymDataGroupByMachVo.getStartTime().substring(0, 7).replace("-", "");
        String end = ymDataGroupByMachVo.getEndTime().substring(0, 7).replace("-", "");
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(start, end);
        } catch (Exception e) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }
        if (dates.size() > 6) {
            return ResultUtil.error(ResultEnum.Err_2034);
        }
        //日期范围查询
        List<String> finalDates = dates;
        query.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });

        query.le("create_time", DateUtils.strToDate(ymDataGroupByMachVo.getEndTime()));
        query.ge("create_time", DateUtils.strToDate(ymDataGroupByMachVo.getStartTime()));

        query.eq("com_id", ymDataGroupByMachVo.getComId());

        if (StringUtils.isNotNull(ymDataGroupByMachVo.getRegionId())) {
            query.eq("region_id", ymDataGroupByMachVo.getRegionId());
        }
        if (StringUtils.isNotNull(ymDataGroupByMachVo.getAreaId())) {
            query.eq("area_id", ymDataGroupByMachVo.getAreaId());
        }
        if (StringUtils.isNotEmpty(ymDataGroupByMachVo.getDeviceNo())) {
            query.eq("device_no", ymDataGroupByMachVo.getDeviceNo());
        }
        query.eq("yct_retcode", "200");
        query.groupBy("device_no");

        Page<MjRecord> mjRecordPage = mapper.selectPage(new Page<MjRecord>(ymDataGroupByMachVo.getPage(), ymDataGroupByMachVo.getSize()), query);

        Map<String, Object> map = new HashMap<>();
        map.put("list", mjRecordPage.getRecords());
        map.put("total", mjRecordPage.getTotal());
        return ResultUtil.success(map);

    }

//    @Override
//    public List<MjRecCountVo> getMjCountByComId(Long beginComId, Long endComId, String month) {
//        return mapper.selectRecCount(beginComId,endComId,month);
//    }

    @Override
    public List<MjRecord> getMjCountByComId(Long beginComId, Long endComId, String month) {
        QueryWrapper<MjRecord> query = Wrappers.query();
        query.select("com_id", "count(id) as mjRecNum");
//        query.between("com_id",beginComId,endComId);
        query.ge("com_id", beginComId);
        query.le("com_id", endComId);
        query.eq("month_value", month);
        query.groupBy("com_id");
        List<MjRecord> list = mapper.selectList(query);
        return list;
    }

    @Override
    public Integer getMjCount(Long beginComId, Long endComId, String month) {
        QueryWrapper<MjRecord> query = Wrappers.query();
//        query.between("com_id",beginComId,endComId);
        query.ge("com_id", beginComId);
        query.le("com_id", endComId);
        query.eq("month_value", month);
        return mapper.selectCount(query);
    }

    @Override
    public Result getMjCount(Long comId, String beginMonth, String endMonth) {
        List<String> dates = new ArrayList<>();

        try {
            dates = DateUtils.getMonthBetween(beginMonth, endMonth);
        } catch (Exception e) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }
//        if (dates.size() > 6) {
//            return ResultUtil.error(ResultEnum.Err_2034);
//        }
        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();
        if (comId > 0l) {
            queryWrapper.eq("com_id", comId);
        }
        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });
        queryWrapper.select("com_id", "month_value", "count(DISTINCT device_no) as mjMachNum", "count(id) as mjRecNum");
        queryWrapper.groupBy("month_value");
        List<MjRecord> list = mapper.selectList(queryWrapper);

        if (list == null || list.size() == 0) {
            return ResultUtil.success(new ArrayList<>());
        }
        List<MjRecCountVo> mjRecCountVos = new ArrayList<>();

        for (MjRecord mjRecord : list) {
            MjRecCountVo mjRecCountVo = new MjRecCountVo();
            QueryWrapper<MjRecord> qw1 = Wrappers.query();
            if (comId > 0l) {
                qw1.eq("com_id", comId);
            }
            qw1.eq("month_value", mjRecord.getMonthValue());
            qw1.apply("device_no is not null");
            qw1.select("count(DISTINCT (device_no)) as mjMachNum");
            qw1.groupBy("month_value");
            MjRecord mjRecord1 = mapper.selectOne(qw1);

            if (mjRecord1 == null || mjRecord1.getDeviceNo() == null) {
                mjRecCountVo.setMjMachNum(0);
            } else {
                mjRecCountVo.setMjMachNum(mjRecord1.getMjRecNum());
            }

            QueryWrapper<MjRecord> qw2 = Wrappers.query();
            if (comId > 0l) {
                qw2.eq("com_id", comId);
            }
            qw2.eq("month_value", mjRecord.getMonthValue());
            qw2.apply("id is not null");
            qw2.select("count(id) as mjRecNum");
            qw2.groupBy("month_value");
            MjRecord mjRecord2 = mapper.selectOne(qw2);

            if (mjRecord2 == null || mjRecord2.getId() == null) {
                mjRecCountVo.setMjRecNum(0);
            } else {
                mjRecCountVo.setMjRecNum(Math.toIntExact(mjRecord2.getId()));
            }
            mjRecCountVo.setMonthValue(mjRecord.getMonthValue());
            mjRecCountVo.setMjMachNum(mjRecord1.getMjMachNum());
            mjRecCountVo.setMjRecNum(mjRecord2.getMjRecNum());
            mjRecCountVos.add(mjRecCountVo);

        }
        return ResultUtil.success(mjRecCountVos);
    }

    @Override
    public Result getMjCount(Long beginComId, Long endComId, String beginMonth, String endMonth) {
        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(beginMonth, endMonth);
        } catch (Exception e) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }
//        if (dates.size() > 6) {
//            return ResultUtil.error(ResultEnum.Err_2034);
//        }
        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();
//        queryWrapper.between("com_id",beginComId,endComId);
        queryWrapper.ge("com_id", beginComId);
        queryWrapper.le("com_id", endComId);
        List<String> finalDates = dates;
        //日期范围查询
        queryWrapper.and(wrapper -> {
            for (String info : finalDates) {
                wrapper.or().eq("month_value", info);
            }
        });

        queryWrapper.select("month_value", "count(DISTINCT device_no) as mjMachNum", "count(id) as mjRecNum");
        queryWrapper.groupBy("month_value");
        List<MjRecord> list = mapper.selectList(queryWrapper);

        if (list == null || list.size() == 0) {
            return ResultUtil.success(new ArrayList<>());
        }

        List<MjRecCountVo> mjRecCountVos = new ArrayList<>();

        for (MjRecord mjRecord : list) {
            MjRecCountVo MjRecCountVo = new MjRecCountVo();
            MjRecCountVo.setMonthValue(mjRecord.getMonthValue());
            MjRecCountVo.setMjRecNum(mjRecord.getMjRecNum());
            MjRecCountVo.setMjMachNum(mjRecord.getMjMachNum());
            mjRecCountVos.add(MjRecCountVo);
        }
        return ResultUtil.success(mjRecCountVos);
    }

    @Override
    public List<MjRecord> getNosendRecord(Long comId, String beginTime, Integer target, Integer size, List<String> yearDate) {
        return mapper.getNosendRecord(comId, beginTime, target, size, yearDate);
    }

    @Override
    public boolean sendAccessRecord(Long comId, Long id, int target, String monthValue) {
        UpdateWrapper<MjRecord> updateWrapper = Wrappers.update();
        updateWrapper.eq("com_id", comId);
        updateWrapper.eq("id", id);
        updateWrapper.eq("month_value", monthValue);
        updateWrapper.setSql("send_state = send_state + " + target);
        int result = mapper.update(new MjRecord(), updateWrapper);
        return result == 1 ? true : false;
    }

    @Override
    public boolean updateAvatar(UpdateAvatarRequest request) {
        UpdateWrapper<MjRecord> updateWrapper = Wrappers.update();
        updateWrapper.eq("com_id", request.getComId());
        updateWrapper.eq("record_id", request.getId());
        updateWrapper.eq("device_no", request.getMachNo());
        updateWrapper.eq("month_value", request.getMonthValue());
        updateWrapper.set("avatar_url" , request.getAvatarUrl());
        int result = mapper.update(new MjRecord(), updateWrapper);
        return result == 1 ? true : false;
    }

    @Override
    public Object todayAcTotal(LargeScreenRequest request) {
        String day  = request.getParam().getString("day");
        if(day ==null || day.length()!=10){
            return  null;
        }
        String monthValue =day.substring(0,7).replace("-","");

        /**
         * select   count(*)- sum(open_type ) outbound, sum(open_type) inbound
         *  from  mj_record_yyyyMM
         * where  com_id =@companyId
         * and  open_time between @day  and  DATE_ADD(@day, INTERVAL 1 DAY)
         * and  open_state =1 ;
         */
        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();

        queryWrapper.select("count(*) - sum(open_type) as  openNum", "sum(open_type)  as unOpenNum");

        queryWrapper.le("open_time", day + " 23:59:59");
        queryWrapper.ge("open_time", day + " 00:00:00");
        queryWrapper.eq("month_value", monthValue);
//        queryWrapper.eq("open_state", 1);
        queryWrapper.eq("com_id", Integer.valueOf(request.getCompanyId()));

        MjRecord mjRecord2 = mapper.selectOne(queryWrapper);
        Map<String, Integer> resMap = new HashMap<>();
        resMap.put("inbound",0);
        resMap.put("outbound",0);
        if(mjRecord2!=null){
            resMap.put("inbound",mjRecord2.getOpenNum());
            resMap.put("outbound",mjRecord2.getUnOpenNum());
        }
        return resMap;
    }

    @Override
    public Object recentlyAcTotal(LargeScreenRequest request) {

        String day  = request.getParam().getString("day");
        if(day ==null || day.length()!=10){
            return  null;
        }
        String dayBef  = day ;

        Integer days  = request.getParam().getInteger("days");

        if(days > 1){
            Date dayBeford  = DateUtil.addDays(DateUtils.strToDate2(day),-1*(days-1) );
            dayBef = DateUtils.getStrDate(dayBeford,"yyyy-MM-dd");
        }

        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(dayBef.substring(0,7).replace("-",""),
                    day.substring(0,7).replace("-",""));
        } catch (Exception e) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }

        /**
         * select STR_TO_DATE(open_time, '%Y-%m-%d') AS date
         * , count(*)- sum(open_type ) outbound, sum(open_type) inbound
         * from  mj_record_202403
         * where  com_id =@companyId
         * and  open_time between DATE_ADD(@day, INTERVAL -@days  DAY)  and  @day
         * and  open_state =1
         * group  by  STR_TO_DATE(open_time, '%Y-%m-%d')
         * order  by  STR_TO_DATE(open_time, '%Y-%m-%d')
         */
        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();

        queryWrapper.select("STR_TO_DATE(open_time, '%Y-%m-%d') AS dateN ","count(*) - sum(open_type) as  openNum", "sum(open_type)  as unOpenNum");



        queryWrapper.le("open_time", day + " 23:59:59");
        queryWrapper.ge("open_time", dayBef + " 00:00:00");

//        queryWrapper.eq("open_state", 1);
        queryWrapper.eq("com_id", Integer.valueOf(request.getCompanyId()));

        queryWrapper.orderByAsc("dateN");
        queryWrapper.groupBy("dateN");

        List<MjRecord> mjRecord2 = new ArrayList<>();

        for (String info : dates) {
            QueryWrapper<MjRecord> queryWrapper1 = queryWrapper.clone();
            queryWrapper1.eq("month_value", info);
            List<MjRecord> mjRecord22  =   mapper.selectList(queryWrapper1);
            if(mjRecord22!=null && mjRecord22.size()>0){
                mjRecord2.addAll(mjRecord22);
            }
        }


        List<String> contantDays = mjRecord2.stream().map(MjRecord::getDateN).collect(Collectors.toList());


        List<Map> mapList =new ArrayList<>();

        for (int i = days; i > 0; i--) {
            Date dayBeford  = DateUtil.addDays(DateUtils.strToDate2(day),-1*(i-1) );
            String dayd = DateUtils.getStrDate(dayBeford,"yyyy-MM-dd");

            Map<String, Object>  resMap = new HashMap<>();
            resMap.put("date",dayd);
            resMap.put("inbound",0);
            resMap.put("outbound",0);
            if(contantDays.contains(dayd)){
                for (MjRecord mjRecord : mjRecord2){
                    if(mjRecord.getDateN().equals(dayd)){
                        resMap.put("inbound",mjRecord.getOpenNum());
                        resMap.put("outbound",mjRecord.getUnOpenNum());
                        break;
                    }
                }
            }
            mapList.add(resMap);
        }

        return mapList;
    }

    @Override
    public Object todayHighFrequencyAcPerson(LargeScreenRequest request) {

        String day  = request.getParam().getString("day");
        if(day ==null || day.length()!=10){
            return  null;
        }
        Integer total  = request.getParam().getInteger("total");
        if(total ==null || total<=0 || total>50){
            total =20;
        }

        String monthValue =day.substring(0,7).replace("-","");


        /**
         * select  dept_name as class,p_name as name
         * , count(*)- sum(open_type ) outbound, sum(open_type) inbound
         * from  mj_record_202403
         * where  com_id =@companyId
         * and  open_time  between  @day  and  DATE_ADD(@day, INTERVAL 1 DAY)
         * and  open_state =1
         * group  by  p_id
         * order  by  count(*) desc
         * limit  @total;
         */

        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();

        queryWrapper.select("dept_name", "p_name","count(*) - sum(open_type) as  openNum", "sum(open_type)  as unOpenNum");

        queryWrapper.le("open_time", day + " 23:59:59");
        queryWrapper.ge("open_time", day + " 00:00:00");
        queryWrapper.eq("month_value", monthValue);
//        queryWrapper.eq("open_state", 1);
        queryWrapper.eq("com_id", Integer.valueOf(request.getCompanyId()));

        queryWrapper.groupBy("p_id");
        queryWrapper.orderByDesc("count(1)");

        queryWrapper.last("limit "+total) ;

        List<MjRecord> mjRecord2 = mapper.selectList(queryWrapper);


        List<Map> mapList =new ArrayList<>();

        if(mjRecord2!=null){
            for (MjRecord mjRecord:mjRecord2){
                Map<String, Object> resMap = new HashMap<>();
                resMap.put("class",mjRecord.getDeptName());
                resMap.put("name",mjRecord.getPName());
                resMap.put("inbound",mjRecord.getOpenNum());
                resMap.put("outbound",mjRecord.getUnOpenNum());
                mapList.add(resMap);
            }
        }
        return mapList;
    }


    @Override
    public Object recentlyAcTime(LargeScreenRequest request) {

        String day  = request.getParam().getString("day");
        if(day ==null || day.length()!=10){
            return  null;
        }
        String dayBef  = day ;

        Integer days  = request.getParam().getInteger("days");

        Integer areaNo  = request.getParam().getInteger("areaNo");
        Integer deptId  = request.getParam().getInteger("deptId");

        String pName  = request.getParam().getString("pName");


        Integer type  = request.getParam().getInteger("type");
        if(type==null){
            type = 0;
        }


        if(days > 1){
            Date dayBeford  = DateUtil.addDays(DateUtils.strToDate2(day),-1*(days-1) );
            dayBef = DateUtils.getStrDate(dayBeford,"yyyy-MM-dd");
        }

        List<String> dates = new ArrayList<>();
        try {
            dates = DateUtils.getMonthBetween(dayBef.substring(0,7).replace("-",""),
                    day.substring(0,7).replace("-",""));
        } catch (Exception e) {
            return ResultUtil.error(ResultEnum.Err_2033);
        }

        /**
         * select STR_TO_DATE(open_time, '%Y-%m-%d %H') AS date, count(*) allbound
         * from  mj_record_202403
         * where  com_id =@companyId
         * and  open_time between DATE_ADD(@day, INTERVAL -@days  DAY)  and  @day
         * and  open_state =1
         * group  by  STR_TO_DATE(open_time, '%Y-%m-%d %H')
         * order  by  STR_TO_DATE(open_time, '%Y-%m-%d %H')
         */
        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();

        if(type == 0){
            queryWrapper.select("STR_TO_DATE(open_time, '%Y-%m-%d %H') AS dateN ","count(*) as  openNum");
        }else{
            queryWrapper.select("STR_TO_DATE(open_time, '%Y-%m-%d') AS dateN ", "count(*) - sum(open_type) as  openNum",
                    "sum(open_type)  as unOpenNum");
        }

        queryWrapper.le("open_time", day + " 23:59:59");
        queryWrapper.ge("open_time", dayBef + " 00:00:00");

        if (!CommonUtil.isNull(pName)) {
            queryWrapper.eq("p_name", pName);
        }

        if (areaNo != null && areaNo > 0) {
            queryWrapper.eq("area_id", areaNo);
        }

        if (deptId != null && deptId > 0) {
            queryWrapper.eq("dept_id", deptId);
        }

//        queryWrapper.eq("open_state", 1);
        queryWrapper.eq("com_id", Integer.valueOf(request.getCompanyId()));

        queryWrapper.orderByAsc("dateN");
        queryWrapper.groupBy("dateN");


        List<MjRecord> mjRecord2 = new ArrayList<>();

        for (String info : dates) {
            QueryWrapper<MjRecord> queryWrapper1 = queryWrapper.clone();
            queryWrapper1.eq("month_value", info);
            List<MjRecord> mjRecord22  =   mapper.selectList(queryWrapper1);
            if(mjRecord22!=null && mjRecord22.size()>0){
                mjRecord2.addAll(mjRecord22);
            }
        }

        List<String> contantDays = mjRecord2.stream().map(MjRecord::getDateN).collect(Collectors.toList());

        List<Map> mapList =new ArrayList<>();

        for (int i = days; i > 0; i--) {
            Date dayBeford  = DateUtil.addDays(DateUtils.strToDate2(day),-1*(i-1) );
            String dayd = DateUtils.getStrDate(dayBeford,"yyyy-MM-dd");


            if(type==0){
                for (int j =0 ;j< 24; j++){
                    String formattedNumber = String.format("%02d", j );
                    String daydh = dayd +" "+ formattedNumber+":00:00";
                    Map<String, Object>  resMap = new HashMap<>();
                    resMap.put("date",daydh);
                    resMap.put("allbound",0);
                    if(contantDays.contains(daydh)){
                        for (MjRecord mjRecord : mjRecord2){
                            if(mjRecord.getDateN().equals(daydh)){
                                resMap.put("allbound",mjRecord.getOpenNum());
                                break;
                            }
                        }
                    }
                    mapList.add(resMap);

                }


            }else{
                Map<String, Object>  resMap = new HashMap<>();
                resMap.put("date",dayd);
                resMap.put("openNum",0);
                resMap.put("unOpenNum",0);
                if(contantDays.contains(dayd)){
                    for (MjRecord mjRecord : mjRecord2){
                        if(mjRecord.getDateN().equals(dayd)){
                            resMap.put("openNum",mjRecord.getOpenNum());
                            resMap.put("unOpenNum",mjRecord.getUnOpenNum());
                            break;
                        }
                    }
                }
                mapList.add(resMap);
            }




        }

        return mapList;
    }

    @Override
    public Object entryExitRecords(LargeScreenRequest request) {

        //当前小时
        Integer hour = DateUtil.getHour(new Date());

        String monthValue = DateUtils.getStrDate(new Date(), "yyyyMM");
        Integer deptId  = request.getParam().getInteger("deptId");
        Integer areaId  = request.getParam().getInteger("areaId");
        if(deptId==null) deptId = 0 ;
        if(areaId==null) areaId = 0 ;

        String pName  = request.getParam().getString("pName");

        /**
         * SELECT
         *     DATE_FORMAT(open_time, '%Y-%m-%d %H:00:00') AS hour,
         *     area_id,
         *     SUM(CASE WHEN open_type = 1 THEN 1 ELSE 0 END) AS entered_count,
         *     SUM(CASE WHEN open_type = 2 THEN 1 ELSE 0 END) AS exited_count
         * FROM
         *     mj_record_yyyyMM
         * WHERE
         *      area_id = #{areaId}
         *      AND
         * DATE(open_time) = CURDATE()
         * GROUP BY
         *     hour,
         * area_id;
         */

        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();

        queryWrapper.select("DATE_FORMAT(open_time, '%H')  AS dateN ", "area_id" ,
                "count(*) - sum(open_type) as  openNum",
                "sum(open_type)  as unOpenNum");

        String day = DateUtils.getDate();
//        String day = "2024-04-01";

        queryWrapper.le("open_time", day + " 23:59:59");
        queryWrapper.ge("open_time", day + " 00:00:00");

        queryWrapper.eq("com_id", Integer.valueOf(request.getCompanyId()));

        if (!CommonUtil.isNull(pName)) {
            queryWrapper.eq("p_name", pName);
        }

        queryWrapper.orderByAsc("dateN");
        queryWrapper.groupBy("dateN");

        queryWrapper.eq("month_value", monthValue);

        if(deptId!=null && deptId>0){
            queryWrapper.eq("dept_id", deptId);
        }
        if(areaId!=null && areaId>0){
            queryWrapper.eq("area_id", areaId);
        }


        List<MjRecord> mjRecord2 = mapper.selectList(queryWrapper);

        List<EntryExitRecordsVo> exitRecordsVoList =new ArrayList<>();


        for (int j = 0; j < 24; j++) {

            EntryExitRecordsVo entryExitRecordsVo=new EntryExitRecordsVo();
            entryExitRecordsVo.setHour(j);

            if(j>hour){
                exitRecordsVoList.add(entryExitRecordsVo);
                continue;
            }

            for (MjRecord mjRecord:mjRecord2){
                if(Integer.valueOf(mjRecord.getDateN()) == j){
                    entryExitRecordsVo.setEnteredCount(mjRecord.getOpenNum());
                    entryExitRecordsVo.setExitedCount(mjRecord.getUnOpenNum());

                    if(deptId == 0){
                        continue;
                    }

                    //有記錄
                    if(mjRecord.getOpenNum() !=0 ||  mjRecord.getUnOpenNum() !=0 && Integer.valueOf(mjRecord.getDateN()) != j){
                        String canKey = "mjRecord:comId:"+request.getCompanyId()+"#areaId:"+areaId+"#deptId:"+deptId+"#hour:"+j;
                        Object mjRec  = redisUtil.get(canKey);
                        if(mjRec!=null){
                            if (canKey.toString().equals("null")) {
                                continue;
                            }
                            entryExitRecordsVo.setItem((List<EntryExitRecordsVo.EntryExitRecordsItem>) mjRec);
                            continue;
                        }

                        QueryWrapper<MjRecord> queryWrapper1 = new QueryWrapper<>();

                        queryWrapper1.select("p_name","p_code","open_type","dept_name","area_name","open_time");

                        queryWrapper1.le("open_time", day + " "+ mjRecord.getDateN() +":59:59");
                        queryWrapper1.ge("open_time", day + " "+ mjRecord.getDateN() +":00:00");

                        queryWrapper1.eq("com_id", Integer.valueOf(request.getCompanyId()));

                        queryWrapper1.eq("month_value", monthValue);

                        if(deptId!=null && deptId>0){
                            queryWrapper1.eq("dept_id", deptId);
                        }
                        if(areaId!=null && areaId>0){
                            queryWrapper1.eq("area_id", areaId);
                        }
                        queryWrapper1.orderByDesc("open_time");
                        queryWrapper1.last("limit 100");
                        List<MjRecord> mjRecord3 = mapper.selectList(queryWrapper1);

                        if(mjRecord3!=null && mjRecord3.size()>0){
                            for (MjRecord mjRecord1:mjRecord3){
                                EntryExitRecordsVo.EntryExitRecordsItem entryExitRecordsItem =new EntryExitRecordsVo.EntryExitRecordsItem();
                                entryExitRecordsItem.setPName(mjRecord1.getPName());
                                entryExitRecordsItem.setPCode(mjRecord1.getPCode());
                                entryExitRecordsItem.setOpenType(mjRecord1.getOpenType());
                                entryExitRecordsItem.setAreaName(mjRecord1.getAreaName());
                                entryExitRecordsItem.setDeptName(mjRecord1.getDeptName());
                                entryExitRecordsItem.setOpenTime(mjRecord1.getOpenTime());
                                entryExitRecordsVo.getItem().add(entryExitRecordsItem);
                            }
                        }
                        if(entryExitRecordsVo.getItem().size()>0){
                            redisUtil.set(canKey,entryExitRecordsVo.getItem(),86400l);
                        }else{
                            redisUtil.set(canKey,"null",86400l);
                        }
                    }
                    break;
                }
            }

            exitRecordsVoList.add(entryExitRecordsVo);
        }

        return exitRecordsVoList;
    }


    //定时任务数据初始化用

    @Override
    public void addInsideData(AddAttendanceReq request) {

        if (request.getTimeType() != 1) {
            return;
        }

        MjAttendance mjAttendance = new MjAttendance();
        mjAttendance.setComId(request.getComId());
        mjAttendance.setGroupNo(0l);
        mjAttendance.setTimeType(1);
        mjAttendance.setRoleId(request.getId());
        mjAttendance.setRoleName(request.getRuleName());
        mjAttendance.setPId(request.getPId());
        mjAttendance.setAttendanceResult(3);
        mjAttendance.setAttendanceResultName("缺卡");
        mjAttendance.setAttendanceDate(new Date());
        mjAttendance.setDataSource(2);
        mjAttendance.setZoneNo(request.getZoneNo());
        mjAttendance.setAttendanceType(request.getAttendanceType());
        mjAttendance.setCreateTime(new Date());
        if(request.getAttendanceType()==1){
            mjAttendance.setExpectPoint(1);
        }else{
            mjAttendance.setExpectPoint(2);
        }

        String taskTime = DateUtils.getDate() + " " + request.getTaskTime() + ":00";


        //本月和上月
        String monthValue = DateUtils.getStrDate(new Date(), "yyyyMM");
        String lastMonthValue = DateUtils.getLastMonth2();

        mjAttendance.setMonthValue(monthValue);

        QueryWrapper<MjRecord> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("com_id", request.getComId());
        queryWrapper.eq("p_id", request.getPId());
        queryWrapper.in("area_id", request.getAreaIds());
        queryWrapper.last("limit 1");
        queryWrapper.eq("open_state", 1);



        //查上月
        QueryWrapper<MjRecord> queryWrapper1 = queryWrapper.clone();
        queryWrapper1.eq("month_value", monthValue);


        QueryWrapper<MjRecord> queryWrapper2 = queryWrapper.clone();
        queryWrapper2.eq("month_value", lastMonthValue);

        //查正常签到签退记录
        queryWrapper.eq("month_value", monthValue);
        queryWrapper.orderByDesc("open_time");
        queryWrapper.lt("open_time", taskTime);
        MjRecord mjRecord = mapper.selectOne(queryWrapper);
        if (mjRecord == null) {
            mjRecord = mapper.selectOne(queryWrapper1);
            if (mjRecord == null) {
                mjRecord = mapper.selectOne(queryWrapper2);
            }
        }

        mjAttendance.setRegionStatus(0);

        if (mjRecord != null) {
            if(request.getGroupList()!=null && request.getGroupList().size()>0){
                if(mjRecord.getGroupNo()!=null && mjRecord.getGroupNo()>0){
                    for (EntityMjGroupInfo entityMjGroupInfo:request.getGroupList()){
                        if(entityMjGroupInfo.getGroupNo().equals(mjRecord.getGroupNo())){
                            mjAttendance.setGroupName(entityMjGroupInfo.getGroupName());
                            break;
                        }
                    }
                }
            }

            mjAttendance.setDataSource(1);
            //进
            mjAttendance.setRemark("门禁设备【" + mjRecord.getDeviceNo() + "】");
            if (mjRecord.getOpenType() == 1 || mjRecord.getOpenType() == 0) {
                if(request.getAttendanceType()==1){
                    mjAttendance.setRegionStatus(3);
                }else{
                    mjAttendance.setRegionStatus(1);
                }
                mjAttendance.setAttendanceResult(1);
                mjAttendance.setAttendanceResultName("正常");
            } else if (mjRecord.getOpenType() == 2) {
                if(request.getAttendanceType()==1){
                    mjAttendance.setRegionStatus(4);
                }else{
                    mjAttendance.setRegionStatus(2);
                }
                mjAttendance.setAttendanceResult(3);
                mjAttendance.setAttendanceResultName("缺卡");
            } else if (mjRecord.getOpenType() == 0) {
                mjAttendance.setAttendanceResult(0);
                mjAttendance.setAttendanceResultName("未知");
                mjAttendance.setRemark("门禁设备【" + mjRecord.getDeviceNo() + "】进出状态未设置");
            }
            spplementaryData(mjAttendance, mjRecord);
            mjAttendanceService.initInsert(mjAttendance);
            return;
        }

        Result<UserInfoYkmVo> ykmVoResult = humanClientService.GetPersonInfo(request.getComId(), request.getPId());
        if (ykmVoResult.getCode() == 200 && ykmVoResult.getData().getStatus()==0) {
            if (mjRecord == null  && ykmVoResult.getData().getDeptName()!=null && ykmVoResult.getData().getDeptName().equals("已毕业") ) {
                return;
            }
            mjAttendance.setRecordId(0l);
            mjAttendance.setPName(ykmVoResult.getData().getPName());
            mjAttendance.setPCode(ykmVoResult.getData().getPCode());
            if(ykmVoResult.getData().getDeptId()!=null){
                mjAttendance.setDeptId(ykmVoResult.getData().getDeptId().intValue());
            }
            mjAttendance.setDeptName(ykmVoResult.getData().getDeptName());
            mjAttendanceService.initInsert(mjAttendance);
        }
    }



    private MjAttendance spplementaryData(MjAttendance mjAttendance,MjRecord mjRecord){
        mjAttendance.setRecordId(mjRecord.getId());
        mjAttendance.setPName(mjRecord.getPName());
        mjAttendance.setPCode(mjRecord.getPCode());
        mjAttendance.setRecordId(mjRecord.getId());
        mjAttendance.setDeptId(mjRecord.getDeptId());
        mjAttendance.setDeptName(mjRecord.getDeptName());
        mjAttendance.setGroupNo(mjRecord.getGroupNo());
        mjAttendance.setAttendanceDateTime(mjRecord.getOpenTime());
        return mjAttendance;
    }

}
